from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import backref


app = Flask(__name__)
# 连接数据库连接url
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://zhangyz:123456@10.4.7.13:3306/school?charset=utf8mb4"
# 动态追踪修改设置，如未设置只会提示警告
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# 查询时会显示原始SQL语句
app.config["SQLALCHEMY_ECHO"] = True

# 把SQLAlchemy组件注册到项目中
db = SQLAlchemy()
db.init_app(app)


class Student(db.Model):
    __tablename__ = "t_1v1_student"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    name = db.Column(db.String(15), index=True, comment="姓名")
    age = db.Column(db.SmallInteger, comment="年龄")
    sex = db.Column(db.Boolean, default=True, comment="性别")
    email = db.Column(db.String(128), unique=True, comment="邮箱地址")
    money = db.Column(db.Numeric(10, 2), default=0.0, comment="钱包")

    def __repr__(self):
        return f"<{self.name} {self.__class__.__name__}>"

class StudentInfo(db.Model):
    __tablename__ = "t_1v1_student_info"
    id = db.Column(db.Integer, primary_key=True, comment="主键")
    # 外键字段（提供给数据库）
    student_id = db.Column(db.Integer, db.ForeignKey("t_1v1_student.id"), comment="student外键")
    # 关联属性（提供给SQLALchemy使用，这个字段不是给数据库使用的）
    student = db.relationship("Student", uselist=False, backref=backref("info", uselist=False))

    address = db.Column(db.String(255), comment="注册地址")
    mobile = db.Column(db.String(11), unique=True, comment="手机号码")


@app.route("/")
def index():
    student = Student(
        name="张三",
        age=18,
        sex=True,
        email="zhangsan@qq.com",
        money=10000,
        info=StudentInfo(address="北京市大兴区", mobile="16888888888")
    )
    db.session.add(student)
    db.session.commit()
    return "ok"


@app.route("/index02")
def index02():
    """已经有了主模型，基于主模型新增外键模型"""
    student = Student(name="小红", age=18, sex=False, email="xiaohong@qq.com", money=10000)
    db.session.add(student)
    db.session.commit()

    student.info = StudentInfo(address="北京市昌平区", mobile="16999991999")
    db.session.commit()

    return "ok"


@app.route("/index03")
def index03():
    """添加外键模型的同时，同时新增主模型"""
    info = StudentInfo(
        address="北京市昌平区白沙路",
        mobile="16999991991",
        student=Student(name="小明", age=18, sex=False, email="xiaoming@qq.com", money=10000)
    )
    db.session.add(info)
    db.session.commit()

    return "ok"


@app.route("/query_data01")
def query_data01():
    """查找关联的表数据"""
    # 通过主模型查找外键模型
    student = Student.query.filter(Student.name=="张三").first()
    print(student.info)
    print(student.info.mobile)

    return "ok"


@app.route("/query_data02")
def query_data02():
    """通过外键模型查找主模型数据"""
    info = StudentInfo.query.filter(StudentInfo.mobile=="16888888888").first()
    print(info.student)
    print(info.student.name)

    return "ok"

@app.route("/query_data03")
def query_data03():
    """通过外键模型的字段作为主模型的查找条件"""
    student = Student.query.filter(StudentInfo.mobile=="16888888888").first()
    print(student)

    return "ok"

@app.route("/query_data04")
def query_data04():
    """以主键模型字段作为外键模型的查找条件"""
    info = StudentInfo.query.filter(Student.name=="张三").first()
    print(info)

    return "ok"


@app.route("/update_data01")
def update_data01():
    """根据主模型修改附加模型的数据"""
    student = Student.query.filter(Student.name=="小明").first()
    student.age = 30
    student.info.address = "北京市密云区"
    db.session.commit()

    return "ok"

@app.route("/update_data02")
def update_data02():
    """根据外键模型修改主模型的数据"""
    info = StudentInfo.query.filter(StudentInfo.mobile=="16999991999").first()
    info.student.age = 15
    db.session.commit()

    return "ok"


@app.route("/delete_data01")
def delete_data01():
    """删除主模型中的数据操作"""
    student = Student.query.get(5)
    db.session.delete(student)
    db.session.commit()

    return "ok"


if __name__ == '__main__':
    with app.app_context():
        db.create_all()
    app.run(host='0.0.0.0', port=5000, debug=True)